clear all
set more off , perm
capture log close

********************************************************************************
***************** This file loads the Weekly Oil Bulletin data ****************
********************************************************************************

	
****************************************************
**         Data load: 27 European countries       **
****************************************************	

// Load Oil Bulletin price data
	 
	import excel "$raw\02_Other_Datasets\Oil_Bulletin_Prices_History.xls" , clear sheet("Prices wo taxes, per CTR") first
	
	** Fill in country names
		replace A=A[_n-1] if A==""

	** Rename variables
		rename A country
		rename B date1
		rename D netp_e5
		rename E netp_diesel
		
	** Keep necessary variables
		keep country date1 netp_e5 netp_diesel
		
	** Reformat variables
		keep if date1 !=""
		gen date = date(date1, "DMY")
		format date %td
		drop if date==.
		drop date1
		destring netp_e5 , replace
		replace netp_e5 = netp_e5 / 1000
		destring netp_diesel , replace
		replace netp_diesel = netp_diesel / 1000
		
	** Only keep 2013 and 2014
		keep if year(date) == 2013 | year(date) == 2014	
	
	** Save data 
		save "$dta\oil_bulletin_all", replace
		
		
// Load oil prices
	use "$dta\rotterdam_price", clear
	
	** Merge dates from oil bulletin
		merge 1:m date using "$dta\oil_bulletin_all"
		keep date p_rotterdam _merge
		duplicates drop 

	** Create group identifier to compute weekly average oil price
		sort date
		gen week = week(date)
		replace week = week + 1 if _merge == 3
		replace week = week + 1 if year(date) == 2014 & _merge[_n-1] == 3
		replace week = week + 52 if year(date) == 2014
		
	** Compute average oil price per week
		bysort week: egen p_rott_weekly = mean(p_rotterdam)
	
	** Keep and save
		keep if _merge == 3
		keep date p_rott_weekly
		save "$dta\oilprice_temp.dta" , replace	
	
	
// Merge fuel prices with oil price data
		use "$dta\oil_bulletin_all.dta" , clear
		merge m:1 date using "$dta\oilprice_temp.dta"
		assert _merge == 3
		drop _merge
	
	
// Regression analysis

	* Drop the week of 9 Sep 2013: half of the week is before the MTU, half after. Prices are available at weekly level
		drop if date==d(09sep2013)

	* Generate treatment variable
		gen treatment_group = 0
		replace treatment_group = 1 if country == "DE"
		gen treatment_period = 0
		replace treatment_period = 1 if date >= date("20130912","YMD")
		gen treated = treatment_group * treatment_period
		egen country_2 = group(country)
		
	* Gen log net prices
		gen lognetp_e5 = log(netp_e5)
		gen lognetp_diesel = log(netp_diesel)

	* Run regression analysis		
		eststo e5: reghdfe lognetp_e5 treated if date > d(12apr2013) & date < d(31mar2014) , absorb(i.date i.country_2)	cl(country_2)
		eststo diesel: reghdfe lognetp_diesel treated if date > d(12apr2013) & date < d(31mar2014) , absorb(i.date i.country_2) cl(country_2)
		eststo e5rottp: reghdfe lognetp_e5 treated country_2##c.p_rott_weekly if date > d(12apr2013) & date < d(31mar2014) , absorb(i.date i.country_2) 
		eststo dieselrottp: reghdfe lognetp_diesel treated country_2##c.p_rott_weekly if date > d(12apr2013) & date < d(31mar2014) , absorb(i.date i.country_2) 
		
	// Gen Table C6: Effect of MPD on the logarithm of net prices
		esttab e5 diesel e5rottp dieselrottp using "$output\Table C6_Regression Results_oil_market_bulletin.tex" , b(3) se(3) ar2 replace booktabs star(* 0.10 ** 0.05 *** 0.01) keep(treated) label ///
			title(Effect of MPD on the logarithm of net prices \label{tab1}) ///
			nonumbers mtitles("(1) E5" "(2) Diesel" "(3) E5 (oil price)" "(4) Diesel (oil price)") 
			
			
// Erase data 
erase "$dta\oil_bulletin_all.dta"	
erase "$dta\oilprice_temp.dta" 		
		
	
